package com.g2room.driving.grap.article.qg;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.g2room.driving.grap.article.to.ArticlesTo;
import com.g2room.driving.util.DbUtil;

public class GenericArti {
	static Connection connection = null;
	static PreparedStatement  ps = null;
	public static Map<String, String> errorUrls = new HashMap<String, String>();
	static List<String> titles = getTitles();
	
	static int sqlCount = 0;
	public static void insert2Db(ArticlesTo article) {
		try {
			if(sqlCount == 0) ps = DbUtil.getQgshConn().prepareStatement("insert into d_article_grap(title,content,publish_time,type_name,source,source_url,state,thumb,tag) values(?,?,?,?,?,?,?,?,?)");
			ps.setString(1, article.getTitle());
			ps.setString(2, article.getContent());
			ps.setString(3, article.getPublishTime());
			ps.setString(4, article.getTypeName());
			ps.setString(5, article.getSource());
			ps.setString(6, article.getSourceUrl());
			ps.setInt(7, article.getState());
			ps.setString(8, article.getThumb());
			ps.setString(9, article.getTag());
			ps.execute();
			
//			ResultSet rs = ps.getGeneratedKeys(); //获取结果   
//			if (rs.next()) {
//				ids.add(rs.getInt(1));
//			}
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void insertError2Db(String soruce) {
		try {
			PreparedStatement  ps = DbUtil.getQgshConn().prepareStatement("insert into d_article_grap_err(title,source,source_url,type_name) values(?,?,?,?)");
			for(String url : errorUrls.keySet()) {
				ps.setString(1, errorUrls.get(url));
				ps.setString(2, soruce);
				ps.setString(3, url);
				ps.setString(4, "导航和列表错误");
				ps.addBatch();
			}
			ps.executeBatch();
			if(null != ps) ps.close();
			DbUtil.closeConn();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	public static String handleSpace(String str) {
		return str.replaceAll("&nbsp;", " ");
	}
	
	public static List<Integer> getLatestIds(String publishTime, String soruce) {
		List<Integer> ids = new ArrayList<Integer>();
		try {
			Connection connection = DbUtil.getQgshConn();
			Statement statement = connection.createStatement();
			String sql = "select id from d_article_grap "
					+ "where create_time>'" + publishTime + "' and source='" + soruce + "'";
			ResultSet rs = statement.executeQuery(sql);
			while (rs.next()) {
				ids.add(rs.getInt("id"));
			}
			rs.close();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return ids;
	}
	
	public static List<String> getTitles() {
		String sql = "select a.title from d_article_grap a";
		List<String> articles = new ArrayList<String>();
		try {
			Connection connection = DbUtil.getQgshConn();
			Statement statement = connection.createStatement();
			ResultSet rs = statement.executeQuery(sql);
			while (rs.next()) {
				articles.add(rs.getString("title"));
			}
			rs.close();
			statement.close();
//			connection.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return articles;
	}

	public static Date getPublishTime(int id) {
		Date date = null;
		try {
			Connection connection = DbUtil.getQgshConn();
			Statement statement = connection.createStatement();
			String sql = "select publish_time from d_update where id=" + id;
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next()) {
				date = rs.getTimestamp("publish_time");
			}
			rs.close();
			statement.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
		return date;
	}

	public static String getPublishTimeStr(int id) {
		String publishTime="";
		try {
			Date date = getPublishTime(id);
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			publishTime = format.format(date);
		} catch(Exception e) {
			e.printStackTrace();
		}
		return publishTime;
	}
	
	public static void updateTime(int id) {
		try {
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String publishTime = format.format(new Date());
			Connection connection = DbUtil.getQgshConn();
			Statement statement = connection.createStatement();
			String sql = "update d_update set publish_time = '"+publishTime+"' where id=" + id;
			statement.execute(sql);
			statement.close();
			
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
}
